<!DOCTYPE html><html><head><meta charset="utf-8"><meta name="X-UA-Compatible" content="IE=edge"><meta name="baidu-site-verification" content="1EB8XoOl0C"><meta name="google-site-verification" content="K7thEgdLm0UfRWJ5MGdF7sCcjClSzAlxFLPv2Oz5CGM"><title> Mysql基础 · 页面仔小杨</title><meta name="viewport" content="width=device-width, initial-scale=1"><meta name="description" content="Mysql基础 - 页面仔小杨"><meta name="keywords" content="Java"><meta name="author" content="页面仔小杨"><link rel="short icon" href="/images/favicon.ico"><link rel="stylesheet" href="/css/bubuzou.css"><link rel="search" type="application/opensearchdescription+xml" href="http://itxing666.gitee.io/atom.xml" title="页面仔小杨"><script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src='//unpkg.com/valine/dist/Valine.min.js'></script><meta name="generator" content="Hexo 4.2.0"><link rel="alternate" href="/atom.xml" title="页面仔小杨" type="application/atom+xml">
</head><body><header><div class="header row"> <a href="/" class="logo-link"><img src="/images/logo.png"></a><ul id="nav_list" class="nav nav-list"><li class="nav-list-item"><a href="/" target="_self" data-hover="博文" class="nav-list-link">博文</a></li><li class="nav-list-item"><a href="/archives/" target="_self" data-hover="归档" class="nav-list-link">归档</a></li><li class="nav-list-item"><a href="/about/" target="_self" data-hover="关于" class="nav-list-link">关于</a></li></ul><div class="search"><a id="search_btn" href="#search"></a></div><div id="nav_btn" class="nav-btn"><span></span><span></span><span></span></div></div></header><div class="row scroll-con"><section class="container"><!-- for archive page--><div id="postAr" class="post"><article class="post-block"><h1 class="post-title">Mysql基础</h1><div class="post-info">2021-08-23</div><div class="post-content"><p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/343.png" alt="343"></p>
<h2 id="什么是数据库"><a href="#什么是数据库" class="headerlink" title="什么是数据库"></a>什么是数据库</h2><p>数据库：DB DataBase</p>
<p>概念：数据仓库、软件、安装在操作系统(window linux mac)之上,可以存储大量的数据</p>
<p>作用：存储数据 管理数据</p>
<h2 id="数据库分类"><a href="#数据库分类" class="headerlink" title="数据库分类"></a>数据库分类</h2><p>关系型数据库：</p>
<ul>
<li>MySql Oracle Sql Server DB2 SQLlite</li>
<li>通过表和表之间，行和列之间的关系进行数据的存储</li>
</ul>
<p>非关系型数据库：</p>
<ul>
<li>Redis MongDB</li>
<li>非关系型数据库 对象存储 通过对象的自身的属性来决定</li>
</ul>
<h2 id="DBMS（数据库管理软件）"><a href="#DBMS（数据库管理软件）" class="headerlink" title="DBMS（数据库管理软件）"></a>DBMS（数据库管理软件）</h2><ul>
<li>数据库管理软件 科学有效的管理我们的数据 维护和获取数据</li>
</ul>
<h2 id="MySQL简介"><a href="#MySQL简介" class="headerlink" title="MySQL简介"></a>MySQL简介</h2><p>体积小 速度快 成本低</p>
<p><a href="https://www.mysql.com/" target="_blank" rel="noopener">https://www.mysql.com/</a></p>
<h2 id="安装MySQL"><a href="#安装MySQL" class="headerlink" title="安装MySQL"></a>安装MySQL</h2><p><a href="https://www.jianshu.com/p/4fc53d7d7620" target="_blank" rel="noopener">https://www.jianshu.com/p/4fc53d7d7620</a></p>
<h2 id="终端查询mysql版本-登录"><a href="#终端查询mysql版本-登录" class="headerlink" title="终端查询mysql版本/登录"></a>终端查询mysql版本/登录</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql --version</span><br><span class="line"></span><br><span class="line">mysql -u root -p</span><br><span class="line"></span><br><span class="line">exit; &#x2F;&#x2F; 退出</span><br><span class="line"></span><br><span class="line">&#x2F;&#x2F; navicat commond+R 运行</span><br><span class="line">&#x2F;&#x2F; navicat创建数据库、创建表 在日志中可查看相关sql语句</span><br></pre></td></tr></table></figure>

<a id="more"></a>

<h2 id="数据库语言"><a href="#数据库语言" class="headerlink" title="数据库语言"></a>数据库语言</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">DDL 数据库定义语言</span><br><span class="line">DML 数据库操作语言</span><br><span class="line">DQL 数据库查询语言</span><br><span class="line">DCL 数据库控制语言</span><br></pre></td></tr></table></figure>



<h2 id="基础命令"><a href="#基础命令" class="headerlink" title="基础命令"></a>基础命令</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">databases</span>;  <span class="comment">-- 查看所有的数据库</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">use</span> 数据库名;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> detabase();  <span class="comment">-- 查看当前使用的数据库</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">tables</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">tables</span> <span class="keyword">from</span> 库名;</span><br><span class="line"></span><br><span class="line">desc 表名;  <span class="comment">-- 查看表结构</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> westos; <span class="comment">-- 创建数据库</span></span><br></pre></td></tr></table></figure>



<h2 id="操作数据库（库、表、字段）"><a href="#操作数据库（库、表、字段）" class="headerlink" title="操作数据库（库、表、字段）"></a>操作数据库（库、表、字段）</h2><h3 id="操作库"><a href="#操作库" class="headerlink" title="操作库"></a>操作库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> <span class="keyword">if</span> <span class="keyword">not</span> <span class="keyword">exists</span> westos;  <span class="comment">-- 创建数据库</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">database</span> <span class="keyword">if</span> <span class="keyword">exists</span> hello;  <span class="comment">-- 删除数据库</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 如果表名、字段名是一个特殊符号，需要加``</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">use</span> <span class="string">`test`</span>;  <span class="comment">-- 使用数据库</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="string">`user_name`</span> <span class="keyword">from</span> <span class="string">`user`</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> databses; <span class="comment">-- 查看所有的数据库</span></span><br></pre></td></tr></table></figure>



<h3 id="数据库的数据类型"><a href="#数据库的数据类型" class="headerlink" title="数据库的数据类型"></a>数据库的数据类型</h3><p><strong>数值</strong></p>
<ul>
<li><p>tinyint  十分小的数据          1个字节</p>
</li>
<li><p>smallint  较小的数据           2个字节</p>
</li>
<li><p>mediumint 中等数据          3个字节</p>
</li>
<li><p><strong>int       标准的整数           4个字节 （常用的）</strong></p>
</li>
<li><p>bigint   较大的数据           8个字节</p>
</li>
<li><p>float     浮点数                   4个字节</p>
</li>
<li><p>double 浮点数                   8个字节（精度问题）</p>
</li>
<li><p>decimal  字符串形式的浮点数  金融计算的时候，一般用decimal</p>
</li>
</ul>
<p><strong>字符串</strong></p>
<ul>
<li>char 字符串,固定大小的      0-255</li>
<li><strong>varchar  可变字符串           0-65535    （常用的 string）</strong></li>
<li>tinytext  微型文本     2^8-1</li>
<li><strong>text 文本串             2^16-1     保存大文本</strong></li>
</ul>
<p><strong>时间日期</strong></p>
<p>java.util.Date</p>
<ul>
<li>date YYYY-MM-DD,日期格式</li>
<li>time HH:mm:ss,时间格式</li>
<li><strong>datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式</strong></li>
<li>timestamp 时间戳 也较为常用</li>
<li>year</li>
</ul>
<p><strong>null</strong></p>
<ul>
<li>没有值，未知</li>
<li>不要使用null进行运算，结果为null</li>
</ul>
<h3 id="数据库的字段属性（重点）"><a href="#数据库的字段属性（重点）" class="headerlink" title="数据库的字段属性（重点）"></a>数据库的字段属性（重点）</h3><ul>
<li><p>Unsigned</p>
<p>无符号的整数</p>
<p>声明了该列不能为负数</p>
</li>
<li><p>zerofill</p>
<p>0填充的</p>
<p>不足的位数使用0来填充</p>
</li>
<li><p>自增</p>
<p>通常理解为自增，自动在上一条记录的基础上+1（默认）</p>
<p>通常用来设计唯一的主键-index，必须是整数类型</p>
<p>可以设置起始值和步长</p>
</li>
<li><p>非空 not null</p>
<p>假设设置为 not null, 如果不给它赋值，就会报错</p>
<p>null 如果不填值，默认就是null</p>
</li>
<li><p>默认值</p>
<p>设置默认值</p>
</li>
</ul>
<p>  每个表都必须存在的5个字段</p>
<blockquote>
<p>id 主键</p>
<p>version 乐观锁</p>
<p>is_delete 伪删除</p>
<p>gmt_create 创建事件</p>
<p>gmt_update 修改事件</p>
</blockquote>
<h3 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">-- 表名称和字段尽量使用&#96;&#96;</span><br><span class="line">-- auto_increment 自增</span><br><span class="line">-- 字符串使用 单引号</span><br><span class="line">-- primary key(&#96;主键&#96;)， 一般一个表只有一个唯一的主键</span><br><span class="line"></span><br><span class="line">create table if not exists &#96;student&#96; (</span><br><span class="line">&#96;id&#96; int(4) not null auto_increment comment &#39;学号&#39;,</span><br><span class="line">&#96;name&#96; varchar(30) not null default &#39;匿名&#39; comment &#39;姓名&#39;,</span><br><span class="line">&#96;pwd&#96; varchar(20) not null default &#39;123456&#39; comment &#39;密码&#39;,</span><br><span class="line">&#96;sex&#96; varchar(2) not null default &#39;男&#39; comment &#39;性别&#39;,</span><br><span class="line">&#96;birthday&#96; datetime default null comment &#39;出生日期&#39;,</span><br><span class="line">&#96;address&#96; varchar(100) default null comment &#39;家庭住宅&#39;,</span><br><span class="line">&#96;email&#96; varchar(20) default null comment &#39;邮箱&#39;,</span><br><span class="line">primary key(&#96;id&#96;)</span><br><span class="line">) engine &#x3D; innodb default charset&#x3D;utf8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">show create database &#96;test&#96;; -- 查看创建数据库语句</span><br><span class="line">show create table &#96;user&#96;; -- 查看创建表的语句</span><br><span class="line">desc &#96;user&#96;; -- 查看表结构</span><br></pre></td></tr></table></figure>

<p>格式</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">create table [if not exists] 表名 (</span><br><span class="line"> &#96;字段名&#96; 列类型 [属性] [索引] [注释],</span><br><span class="line"> &#96;字段名&#96; 列类型 [属性] [索引] [注释],</span><br><span class="line"> &#96;字段名&#96; 列类型 [属性] [索引] [注释],</span><br><span class="line">  primary key(&#96;主键&#96;)</span><br><span class="line">) [表类型] [字符集设置] [注释];</span><br></pre></td></tr></table></figure>



<h3 id="MyIASM与InnoDB区别"><a href="#MyIASM与InnoDB区别" class="headerlink" title="MyIASM与InnoDB区别"></a>MyIASM与InnoDB区别</h3><table>
<thead>
<tr>
<th align="center"></th>
<th align="center">MyIASM</th>
<th align="center">InnoDB</th>
</tr>
</thead>
<tbody><tr>
<td align="center">事务支持</td>
<td align="center">不支持</td>
<td align="center">支持</td>
</tr>
<tr>
<td align="center">数据行锁定</td>
<td align="center">不支持</td>
<td align="center">支持</td>
</tr>
<tr>
<td align="center">外键约束</td>
<td align="center">不支持</td>
<td align="center">支持</td>
</tr>
<tr>
<td align="center">全文索引</td>
<td align="center">支持</td>
<td align="center">不支持</td>
</tr>
<tr>
<td align="center">表空间的大小</td>
<td align="center">较小</td>
<td align="center">较大，约为2倍</td>
</tr>
</tbody></table>
<p>常规使用操作：</p>
<ul>
<li>MyIASM 节约空间，速度较快</li>
<li>InnoDB 安全性高，事务的处理，多表多用户操作</li>
</ul>
<h3 id="修改删除表"><a href="#修改删除表" class="headerlink" title="修改删除表"></a>修改删除表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改表名： alter table 旧表名 rename as 新表名;</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`user`</span> <span class="keyword">rename</span> <span class="keyword">as</span> <span class="string">`users`</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 增加表的字段 alter table 表名 add 字段名 列属性;</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`users`</span> <span class="keyword">add</span> age <span class="built_in">int</span>(<span class="number">11</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 修改表的字段（重命名，修改约束）</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`users`</span> <span class="keyword">modify</span> age <span class="built_in">varchar</span>(<span class="number">11</span>);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`users`</span> <span class="keyword">change</span> age age1;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`users`</span> <span class="keyword">change</span> age age1 <span class="built_in">int</span>(<span class="number">1</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除表的字段</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`users`</span> <span class="keyword">drop</span> age1;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除表</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> <span class="keyword">if</span> <span class="keyword">exists</span> <span class="string">`users`</span>;</span><br></pre></td></tr></table></figure>



<h2 id="MySql数据管理"><a href="#MySql数据管理" class="headerlink" title="MySql数据管理"></a>MySql数据管理</h2><h3 id="外键（了解即可）"><a href="#外键（了解即可）" class="headerlink" title="外键（了解即可）"></a>外键（了解即可）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建表的时候没有外键关系</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`student`</span> <span class="keyword">add</span> <span class="keyword">constraint</span> <span class="string">`KF_gradeid`</span> <span class="keyword">foreign</span> <span class="keyword">key</span>(<span class="string">`gradeid`</span>)</span><br><span class="line"><span class="keyword">references</span> <span class="string">`grade`</span>(<span class="string">`gradeid);</span></span><br></pre></td></tr></table></figure>

<p>以上的操作都是物理外键，数据库级别的外键，我们不建议使用。（避免数据库过多造成干扰）</p>
<p><strong>最佳实践</strong></p>
<ul>
<li>数据库就是单纯的表，只是来存数据</li>
<li>我们想使用多张表的数据，想使用外键（程序去实现）</li>
</ul>
<h3 id="DML语言"><a href="#DML语言" class="headerlink" title="DML语言"></a>DML语言</h3><p>数据库意义：数据存储、数据管理</p>
<p>DML语言：数据操作语言</p>
<ul>
<li><p>insert</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 插入语句</span></span><br><span class="line"><span class="comment">-- insert into 表名(字段名1,字段名2,字段名3) values ('值1','值2','值3');</span></span><br><span class="line"><span class="comment">-- 如果不写字段，将会一一匹配</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`user`</span>(uaer_name, department_id) <span class="keyword">values</span> (<span class="string">'test'</span>, <span class="number">1</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入多条数据</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`user`</span> (user_name, department_id) <span class="keyword">values</span> (<span class="string">'test'</span>, <span class="number">1</span>),(<span class="string">'test2'</span>, <span class="number">2</span>);</span><br></pre></td></tr></table></figure>



</li>
</ul>
<ul>
<li><p>update</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- update table set column = value where some_column = some_value;</span></span><br><span class="line"><span class="comment">-- 不指定条件的情况下，会改动表中的所有数据</span></span><br><span class="line"><span class="keyword">update</span> <span class="string">`user`</span> <span class="keyword">set</span> <span class="string">`user_name`</span> = <span class="string">'test2'</span> <span class="keyword">where</span> <span class="string">`user_name`</span> = <span class="string">'test'</span>;</span><br></pre></td></tr></table></figure>

<p>– where 子句 (假设A=10,B=20)</p>
<table>
<thead>
<tr>
<th align="center">操作符</th>
<th align="center">描述</th>
<th align="center">实例</th>
<th align="center">结果</th>
</tr>
</thead>
<tbody><tr>
<td align="center">=</td>
<td align="center">等于</td>
<td align="center">A=B</td>
<td align="center">false</td>
</tr>
<tr>
<td align="center">&lt;&gt;或!=</td>
<td align="center">不等于</td>
<td align="center">A!=B</td>
<td align="center">true</td>
</tr>
<tr>
<td align="center">&gt;</td>
<td align="center">大于</td>
<td align="center">A&gt;B</td>
<td align="center">false</td>
</tr>
<tr>
<td align="center">&lt;</td>
<td align="center">小于</td>
<td align="center">A&lt;B</td>
<td align="center">true</td>
</tr>
<tr>
<td align="center">&gt;=</td>
<td align="center">大于等于</td>
<td align="center">A&gt;=B</td>
<td align="center">false</td>
</tr>
<tr>
<td align="center">&lt;=</td>
<td align="center">小于等于</td>
<td align="center">A&lt;=B</td>
<td align="center">true</td>
</tr>
<tr>
<td align="center">between…and…</td>
<td align="center">[]在某个范围</td>
<td align="center"></td>
<td align="center"></td>
</tr>
<tr>
<td align="center">and</td>
<td align="center">多个条件（并）</td>
<td align="center"></td>
<td align="center"></td>
</tr>
<tr>
<td align="center">or</td>
<td align="center">多个条件（或）</td>
<td align="center"></td>
<td align="center"></td>
</tr>
</tbody></table>
</li>
</ul>
<ul>
<li><p>delete</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- delete from table where `user_name` = 'test'</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- truncate </span></span><br><span class="line"><span class="comment">-- truncate `user`</span></span><br><span class="line"></span><br><span class="line">相同点：都能删除数据，不会删除表结构</span><br><span class="line">不同点：1.truncate重新设置自增列 计数器会归零</span><br><span class="line">       <span class="number">2.</span><span class="keyword">truncate</span>不会影响事务</span><br><span class="line"></span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> 在功能上与不带 <span class="keyword">WHERE</span> 子句的 <span class="keyword">DELETE</span> 语句相同</span><br><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 <span class="keyword">DELETE</span>。如果要删除表定义及其数据，请使用 <span class="keyword">DROP</span> <span class="keyword">TABLE</span> 语句</span><br></pre></td></tr></table></figure>

<p>了解即可：delete删除的问题，重启数据库现象</p>
<ul>
<li>InnoDB 自增列会从1开始（存在内存当中，断电即失）</li>
<li>MyIASM 继续从上一个自增量开始（存在文件中的，不会丢失）</li>
</ul>
</li>
</ul>
<h3 id="DQL查询语言"><a href="#DQL查询语言" class="headerlink" title="DQL查询语言"></a>DQL查询语言</h3><p>数据准备：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> <span class="keyword">if</span> <span class="keyword">not</span> <span class="keyword">exists</span> <span class="string">`school`</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">use</span> <span class="string">`school`</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">database</span>();</span><br><span class="line"></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> <span class="keyword">if</span> <span class="keyword">exists</span> <span class="string">`grade`</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="string">`grade`</span> (</span><br><span class="line"><span class="string">`gradeid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="literal">null</span> auto_increment <span class="keyword">comment</span> <span class="string">'年级编号'</span>,</span><br><span class="line"><span class="string">`gradename`</span> <span class="built_in">varchar</span>(<span class="number">50</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">comment</span> <span class="string">'年级名称'</span>,</span><br><span class="line">primary <span class="keyword">key</span>(<span class="string">`gradeid`</span>)</span><br><span class="line">) <span class="keyword">engine</span>=<span class="keyword">innodb</span> auto_increment=<span class="number">6</span> <span class="keyword">default</span> <span class="keyword">charset</span>=utf8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`grade`</span> (<span class="string">`gradeid`</span>, <span class="string">`gradename`</span>) <span class="keyword">values</span> (<span class="number">1</span>, <span class="string">'大一'</span>),(<span class="number">2</span>, <span class="string">'大二'</span>),(<span class="number">3</span>, <span class="string">'大三'</span>),(<span class="number">4</span>, <span class="string">'大四'</span>),(<span class="number">5</span>,<span class="string">'预科班'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="string">`grade`</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- todo</span></span><br></pre></td></tr></table></figure>



<h4 id="查询："><a href="#查询：" class="headerlink" title="查询："></a>查询：</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> [<span class="keyword">all</span> | <span class="keyword">distinct</span>] &#123; * | table.* | [table.fiels1[<span class="keyword">as</span> alias1] [,table.fiels2[<span class="keyword">as</span> alias2]][,...]]&#125;</span><br><span class="line"><span class="keyword">from</span> tablename [ad table_alias]</span><br><span class="line">[<span class="keyword">left</span> | <span class="keyword">right</span> | <span class="keyword">inner</span> <span class="keyword">join</span> tablename2] <span class="comment">-- 联合查询</span></span><br><span class="line">[<span class="keyword">where</span> ...] <span class="comment">-- 指定结果要满足的条件</span></span><br><span class="line">[<span class="keyword">group</span> <span class="keyword">by</span> ...] <span class="comment">-- 分组</span></span><br><span class="line">[<span class="keyword">having</span>] <span class="comment">-- 过滤分组满足的次要条件</span></span><br><span class="line">[<span class="keyword">order</span> <span class="keyword">by</span> ...] <span class="comment">-- 排序</span></span><br><span class="line">[<span class="keyword">limit</span> &#123;rowcount, <span class="keyword">offset</span>&#125;]</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">--查询全部的学生</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="string">`student`</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询指定字段</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">`student`</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--别名，给结果起一个名字 as （也可以给表起一个别名 from `student` as s）</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span> <span class="keyword">as</span> <span class="string">'学号'</span>, <span class="string">`studentname`</span> <span class="keyword">as</span> <span class="string">'学生姓名'</span> <span class="keyword">from</span> <span class="string">`student`</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 函数 concat(a, b)</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">concat</span>(<span class="string">'姓名：'</span>，studentname) <span class="keyword">as</span> <span class="string">'新名字'</span> <span class="keyword">from</span> <span class="string">`student`</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询一下有哪些同学参加了考试、成绩</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="string">`result`</span>; <span class="comment">-- 查询全部的考试成绩</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span> <span class="keyword">from</span> <span class="string">`result`</span>; <span class="comment">-- 查询有哪些同学参加了考试</span></span><br><span class="line"><span class="comment">-- 发现有重复的数据 去重</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> studentno <span class="keyword">from</span> <span class="string">`result`</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">version</span>(); <span class="comment">-- 查询系统版本（函数）</span></span><br><span class="line"><span class="keyword">select</span> <span class="number">100</span>*<span class="number">3</span><span class="number">-1</span> <span class="keyword">as</span> <span class="string">'计算结果'</span>; <span class="comment">-- 用来计算（表达式）</span></span><br><span class="line"><span class="keyword">select</span> @@auto_increment_increment; <span class="comment">-- 查询自增的步长（变量）</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 所有的学生成绩 + 1 分查看</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentresult`</span> + <span class="number">1</span> <span class="keyword">as</span> <span class="string">'提分后成绩'</span> <span class="keyword">from</span> <span class="string">`result`</span>;</span><br></pre></td></tr></table></figure>



<h4 id="where-条件子句"><a href="#where-条件子句" class="headerlink" title="where 条件子句"></a>where 条件子句</h4><p>作用：检索数据中符合条件的值</p>
<p>搜索条件由一个或者多个表达式组成，结果布尔值</p>
<ul>
<li><p>逻辑运算符</p>
<table>
<thead>
<tr>
<th align="center">运算符</th>
<th align="center">语法</th>
<th align="center">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">and  &amp;&amp;</td>
<td align="center">A and B</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">or   ||</td>
<td align="center">A or B</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">not    !</td>
<td align="center">not A</td>
<td align="center"></td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询考试成绩在 95～100之间</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentresult`</span> <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> studentresult &gt;= <span class="number">95</span> <span class="keyword">and</span> studentresult &lt;= <span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentresult`</span> <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> studentresult <span class="keyword">between</span> <span class="number">95</span> <span class="keyword">and</span> <span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询除了1000号学生之外的学生成绩</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentresult`</span> <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> studentno != <span class="number">1000</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`studentresult`</span> <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> <span class="keyword">not</span> studentno = <span class="number">1000</span>;</span><br></pre></td></tr></table></figure>



</li>
</ul>
<ul>
<li><p>模糊查询：比较运算符</p>
<table>
<thead>
<tr>
<th align="center">运算符</th>
<th align="center">语法</th>
<th align="center">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">is null</td>
<td align="center">A is null</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">is not null</td>
<td align="center">B is not null</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">between and</td>
<td align="center">A between 10 and 1000</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">like</td>
<td align="center">A like B</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">in</td>
<td align="center">A in (1, 2, 3…)</td>
<td align="center"></td>
</tr>
</tbody></table>
</li>
</ul>
<ul>
<li><p><strong>模糊查询</strong> </p>
<p>like结合 %(代表0到任意个字符)  _(一个字符)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询姓刘的同学</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">'student'</span> <span class="keyword">where</span> studentname <span class="keyword">like</span> <span class="string">'刘%'</span>;</span><br><span class="line"><span class="comment">-- 查询姓刘同学，名字后面只有一个字</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">'student'</span> <span class="keyword">where</span> studentname <span class="keyword">like</span> <span class="string">'刘_'</span>;</span><br><span class="line"><span class="comment">-- 查询姓刘同学，名字后面有两个字的</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">'student'</span> <span class="keyword">where</span> studentname <span class="keyword">like</span> <span class="string">'刘__'</span>;</span><br><span class="line"><span class="comment">-- 查询名字中有国字的同学 %国%</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">'student'</span> <span class="keyword">where</span> studentname <span class="keyword">like</span> <span class="string">'%国%'</span>;</span><br></pre></td></tr></table></figure>

<p>in (具体的一个或多个值)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询 1001 1002 1003学号的同学</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">`student`</span> <span class="keyword">where</span> studentno <span class="keyword">in</span> (<span class="number">1001</span>, <span class="number">1002</span>, <span class="number">1003</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询在北京的学生</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">`student`</span> <span class="keyword">where</span> address <span class="keyword">in</span> (<span class="string">'北京'</span>);</span><br></pre></td></tr></table></figure>



</li>
</ul>
<ul>
<li><p>is null     is not null</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询地址为空的学生 '' 或 null</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">`student`</span> <span class="keyword">where</span> address = <span class="string">''</span> <span class="keyword">or</span> address <span class="keyword">is</span> <span class="literal">null</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询有出生日期的学生  不为空</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>,<span class="string">`studentname`</span> <span class="keyword">from</span> <span class="string">`student`</span> <span class="keyword">where</span> borndate <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br></pre></td></tr></table></figure>



</li>
</ul>
<p>####联表查询</p>
<p>join</p>
<p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/328.png" alt="328"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询参加了考试的同学（学号、姓名、科目编号、分数）(on或where)</span></span><br><span class="line"><span class="keyword">select</span> s.studentno, studentname, subjectno, studentresult <span class="keyword">from</span> student <span class="keyword">as</span> s <span class="keyword">inner</span> <span class="keyword">join</span> <span class="keyword">result</span> <span class="keyword">as</span> r <span class="keyword">on</span> s.studentno = r.studentno;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> s.studentno, studentname, subjectno, studentresult <span class="keyword">from</span> student <span class="keyword">as</span> s <span class="keyword">left</span> <span class="keyword">join</span> <span class="keyword">result</span> <span class="keyword">as</span> r <span class="keyword">on</span> s.studentno = r.studentno;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> s.studentno, studentname, subjectno, studentresult <span class="keyword">from</span> student <span class="keyword">as</span> s <span class="keyword">right</span> <span class="keyword">join</span> <span class="keyword">result</span> <span class="keyword">as</span> r <span class="keyword">on</span> s.studentno = r.studentno;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询缺考的同学</span></span><br><span class="line"><span class="keyword">select</span> s.studentno, studentname, subjectno, studentresult <span class="keyword">from</span> student <span class="keyword">as</span> s <span class="keyword">left</span> <span class="keyword">join</span> <span class="keyword">result</span> <span class="keyword">as</span> r <span class="keyword">on</span> s.studentno = r.studentno <span class="keyword">where</span> studentresult <span class="keyword">is</span> <span class="literal">null</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询参加考试的同学信息：学号、学生姓名、科目名、分数</span></span><br><span class="line"><span class="keyword">select</span> studentno,studentname,subjectName,subjectresult <span class="keyword">from</span> student s <span class="keyword">right</span> <span class="keyword">join</span> <span class="keyword">result</span> r <span class="keyword">on</span> r.studentno = s.studentno <span class="keyword">inner</span> joinsubject sub <span class="keyword">on</span> r.subjectno = sub.subjectno;</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th align="center">操作</th>
<th align="center">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">inner join</td>
<td align="center">两表都满足的组合</td>
</tr>
<tr>
<td align="center">full outer join</td>
<td align="center">全连 两表相同的组合在一起<br />A表有，B表没有的数据（显示为null）<br />同样B表有 A表没有的显示为(null)</td>
</tr>
<tr>
<td align="center">left join</td>
<td align="center">左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null</td>
</tr>
<tr>
<td align="center">right join</td>
<td align="center">右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null</td>
</tr>
</tbody></table>
<h4 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h4><p>自己的表和自己的表连接，核心：<strong>一张表拆为两张表</strong></p>
<p><a href="https://blog.csdn.net/wsdfym/article/details/90743625" target="_blank" rel="noopener">https://blog.csdn.net/wsdfym/article/details/90743625</a></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">DATABASE</span>();</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="string">`category`</span> (</span><br><span class="line"><span class="string">`categoryid`</span> <span class="built_in">int</span>(<span class="number">3</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">comment</span> <span class="string">'id'</span>,</span><br><span class="line"><span class="string">`pid`</span> <span class="built_in">int</span>(<span class="number">3</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">comment</span> <span class="string">'父id'</span>,</span><br><span class="line"><span class="string">`categoryname`</span> <span class="built_in">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">comment</span> <span class="string">'种类名字'</span>,</span><br><span class="line">primary <span class="keyword">key</span>(<span class="string">`categoryid`</span>)</span><br><span class="line">) <span class="keyword">engine</span>=<span class="keyword">innodb</span> <span class="keyword">charset</span>=utf8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">tables</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">2</span>, <span class="number">1</span>, <span class="string">'信息技术'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">3</span>, <span class="number">1</span>, <span class="string">'软件开发'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">5</span>, <span class="number">1</span>, <span class="string">'美术设计'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">4</span>, <span class="number">3</span>, <span class="string">'数据库'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">8</span>, <span class="number">2</span>, <span class="string">'办公信息'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">6</span>, <span class="number">3</span>, <span class="string">'web开发'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`category`</span> (<span class="string">`categoryid`</span>, <span class="string">`pid`</span>, <span class="string">`categoryname`</span>) <span class="keyword">values</span> (<span class="number">7</span>, <span class="number">5</span>, <span class="string">'ps技术'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="string">`category`</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询父子信息</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> a.categoryname <span class="keyword">as</span> <span class="string">'父栏目'</span>, b.categoryname <span class="keyword">as</span> <span class="string">'子栏目'</span> <span class="keyword">from</span> <span class="string">`category`</span> <span class="keyword">as</span> a,<span class="string">`category`</span> <span class="keyword">as</span> b <span class="keyword">where</span> a.categoryid = b.pid;</span><br></pre></td></tr></table></figure>



<h4 id="分页排序-（limit-order-by）"><a href="#分页排序-（limit-order-by）" class="headerlink" title="分页排序 （limit  order by）"></a>分页排序 （limit  order by）</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 根据成绩降序排序 order by table desc;(asc)</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> s.<span class="string">`studentno`</span>, <span class="string">`studentname`</span>, <span class="string">`subjectname`</span>, <span class="string">`subjectresult`</span> <span class="keyword">from</span> student <span class="keyword">as</span> s <span class="keyword">inner</span> jion <span class="string">`result`</span> r <span class="keyword">on</span> s.studentno = r.studentno <span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`subject`</span> sub <span class="keyword">on</span> r.subjectno = sub.subjectno <span class="keyword">where</span> subjectname = <span class="string">'数据库结构-1'</span> <span class="keyword">order</span> <span class="keyword">by</span> studentresult <span class="keyword">desc</span>; <span class="comment">-- (asc 升序)</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- limit 当前值,页面大小;  ()</span></span><br><span class="line"><span class="keyword">select</span> s.<span class="string">`studentno`</span>, <span class="string">`studentname`</span>, <span class="string">`subjectname`</span>, <span class="string">`subjectresult`</span></span><br><span class="line"><span class="keyword">from</span> student <span class="keyword">as</span> s</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`result`</span> r <span class="keyword">on</span> s.studentno = r.studentno</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`subject`</span> sub <span class="keyword">on</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">where</span> subjectname = <span class="string">'数据库结构-1'</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> studentresult <span class="keyword">asc</span> <span class="keyword">limit</span> <span class="number">0</span>, <span class="number">5</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 第二页 limit 5, 5;   (n-1)*pageSize, pageSize;</span></span><br><span class="line"><span class="comment">-- n 当前页面</span></span><br><span class="line"><span class="comment">-- pageSize 页面大小</span></span><br><span class="line"><span class="comment">-- 总页数=数据总数/页面大小</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 JAVA第一学年 课程成绩排名前十名的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）</span></span><br><span class="line"><span class="keyword">select</span> s.<span class="string">`studentno`</span>, <span class="string">`studentname`</span>, <span class="string">`subjectname`</span>, <span class="string">`studentresult`</span></span><br><span class="line"><span class="keyword">from</span> student <span class="keyword">as</span> s</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`result`</span> <span class="keyword">as</span> r <span class="keyword">on</span> s.studentno = r.studentno</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`subject`</span> sub <span class="keyword">on</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">where</span> subjectname = <span class="string">'JAVA第一学年'</span> <span class="keyword">and</span> studentresult &gt; <span class="number">80</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> studentresult <span class="keyword">desc</span></span><br><span class="line"><span class="keyword">limit</span> <span class="number">0</span> <span class="number">10</span>;</span><br></pre></td></tr></table></figure>



<h4 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h4><p>where (这个值是计算出来的)</p>
<p>本质：在where语句中嵌套一个子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询数据库结构-1 的所有考试成绩（学号、科目名称、成绩）,降序排列</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, r.<span class="string">`subjectno`</span>, <span class="string">`studentresult`</span></span><br><span class="line"><span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">as</span> r</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`subject`</span> <span class="keyword">as</span> sub <span class="keyword">on</span> r.subjectno = sub.subjectno</span><br><span class="line"><span class="keyword">where</span> subjectname = <span class="string">'数据库结构-1'</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> studentresult <span class="keyword">desc</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方式二 子查询 由里及外</span></span><br><span class="line"><span class="keyword">select</span> <span class="string">`studentno`</span>, <span class="string">`subjectno`</span>, <span class="string">`studentresult`</span></span><br><span class="line"><span class="keyword">from</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">where</span> subjectno = (</span><br><span class="line">  <span class="keyword">select</span> sunjectno <span class="keyword">from</span> <span class="string">`subject`</span></span><br><span class="line">  <span class="keyword">where</span> subjectname = <span class="string">'数据库结构-1'</span></span><br><span class="line">)</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> studentresult <span class="keyword">desc</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 分数不小于80分的学生的学号和姓名  科目是高等数学的</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> s.<span class="string">`studentno`</span>, <span class="string">`studentname`</span></span><br><span class="line"><span class="keyword">from</span> <span class="string">`student`</span> <span class="keyword">as</span> s</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="keyword">result</span> <span class="keyword">as</span> r</span><br><span class="line"><span class="keyword">on</span> r.studentno = s.studentno</span><br><span class="line"><span class="keyword">where</span> studentresult &gt;= <span class="number">80</span> <span class="keyword">and</span></span><br><span class="line">subjectno = (</span><br><span class="line">  <span class="keyword">select</span> subjectno <span class="keyword">from</span> <span class="string">`subject`</span> <span class="keyword">where</span> subjectname = <span class="string">'高等数学-2'</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>



<h2 id="MySQL-函数"><a href="#MySQL-函数" class="headerlink" title="MySQL 函数"></a>MySQL 函数</h2><h3 id="常用函数"><a href="#常用函数" class="headerlink" title="常用函数"></a>常用函数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 数学运算</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">ABS</span>(<span class="number">-8</span>); <span class="comment">-- 绝对值</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">CEIL</span>(<span class="number">9.4</span>) <span class="comment">-- 向上取整</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">FLOOR</span>(<span class="number">9.4</span>) <span class="comment">-- 向下取整</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">RAND</span>(); <span class="comment">-- 随机数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">SIGN</span>(<span class="number">-10</span>); <span class="comment">-- 返回参数的符号</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 字符串函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">CHAR_LENGTH</span>(<span class="string">'你好'</span>); <span class="comment">-- 字符串长度</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">CONCAT</span>(<span class="string">'你'</span>, <span class="string">'好'</span>); <span class="comment">-- 字符串拼接</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">INSERT</span>(<span class="string">'你好你好'</span>, <span class="number">3</span>, <span class="number">2</span>, <span class="string">'中国123'</span>); <span class="comment">-- 从某个位置开始替换某个长度</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">LOWER</span>(<span class="string">'HELLO'</span>); <span class="comment">-- 大写转小写</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">UPPER</span>(<span class="string">'hello'</span>); <span class="comment">-- 小写转大写</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">INSTR</span>(<span class="string">'hello'</span>, <span class="string">'o'</span>); <span class="comment">-- 返回字符串中第一次出现的索引</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">REVERSE</span>(<span class="string">'hello'</span>); <span class="comment">-- 反转字符串</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 时间和日期函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">CURRENT_DATE</span>(); <span class="comment">-- 查询当前日期</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">CURRENT_TIME</span>(); <span class="comment">-- 查询当前时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">NOW</span>(); <span class="comment">-- 日期+时分秒</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">LOCALTIME</span>(); <span class="comment">-- 本地时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">SYSDATE</span>(); <span class="comment">-- 系统时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">YEAR</span>(<span class="keyword">NOW</span>()); <span class="comment">-- 年</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 系统</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">SYSTEM_USER</span>(); <span class="comment">-- 用户名</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">USER</span>();</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">VERSION</span>();</span><br></pre></td></tr></table></figure>



<h3 id="聚合函数（常用）"><a href="#聚合函数（常用）" class="headerlink" title="聚合函数（常用）"></a>聚合函数（常用）</h3><table>
<thead>
<tr>
<th>函数名称</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>COUNT()</td>
<td>计数</td>
</tr>
<tr>
<td>SUM()</td>
<td>求和</td>
</tr>
<tr>
<td>AVG()</td>
<td>平均值</td>
</tr>
<tr>
<td>MAX()</td>
<td>最大值</td>
</tr>
<tr>
<td>MIN()</td>
<td>最小值</td>
</tr>
</tbody></table>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询学生人数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">COUNT</span>(studentname) <span class="keyword">from</span> student; <span class="comment">-- 指定列，会忽略null</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">COUNT</span>(*) <span class="keyword">from</span> student;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">COUNT</span>(<span class="number">1</span>) <span class="keyword">from</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- count(*) 和 count(1)和count(列名)区别  </span></span><br><span class="line"><span class="comment">-- 执行效果上：  </span></span><br><span class="line"><span class="comment">-- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  </span></span><br><span class="line"><span class="comment">-- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  </span></span><br><span class="line"><span class="comment">-- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计  </span></span><br><span class="line"><span class="comment">-- 执行效率上：  </span></span><br><span class="line"><span class="comment">-- 列名为主键，count(列名)会比count(1)快  </span></span><br><span class="line"><span class="comment">-- 列名不为主键，count(1)会比count(列名)快  </span></span><br><span class="line"><span class="comment">-- 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  </span></span><br><span class="line"><span class="comment">-- 如果有主键，则 select count（主键）的执行效率是最优的  </span></span><br><span class="line"><span class="comment">-- 如果表只有一个字段，则 select count（*）最优 </span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">SUM</span>(<span class="string">`studentresult`</span>) <span class="keyword">as</span> <span class="string">'总和'</span> <span class="keyword">from</span> <span class="keyword">result</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询不同课程的平均分、最高分、最低分（根据不同课程分组）</span></span><br><span class="line"><span class="keyword">select</span> subjectname, <span class="keyword">AVG</span>(studentresult) <span class="keyword">as</span> 平均分, <span class="keyword">MAX</span>(studentresult) <span class="keyword">as</span> 最高分, <span class="keyword">MIN</span>(studentresult) <span class="keyword">as</span> 最低分</span><br><span class="line"><span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">as</span> r</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> <span class="string">`subject`</span> <span class="keyword">as</span> sub</span><br><span class="line"><span class="keyword">on</span> r.<span class="string">`subjectno`</span> = sub.<span class="string">`subjectno`</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> r.<span class="string">`subjectno`</span>;</span><br><span class="line">having 平均分 &gt; 80;</span><br></pre></td></tr></table></figure>



<h3 id="数据库级别的MD5加密（扩展）"><a href="#数据库级别的MD5加密（扩展）" class="headerlink" title="数据库级别的MD5加密（扩展）"></a>数据库级别的MD5加密（扩展）</h3><p>什么是MD5？</p>
<p>主要增强算法的复杂度和不可逆性</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="string">`testmd5`</span>(</span><br><span class="line"><span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">4</span>) <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> <span class="string">`name`</span> <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> <span class="string">`pwd`</span> <span class="built_in">varchar</span>(<span class="number">50</span>) <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> primary <span class="keyword">key</span>(<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">engine</span>=<span class="keyword">innodb</span> <span class="keyword">charset</span>=utf8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> testmd5 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'zhangsan'</span>, <span class="string">'123456'</span>),(<span class="number">2</span>, <span class="string">'lisi'</span>, <span class="string">'123456'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">update</span> testmd5 <span class="keyword">set</span> pwd = <span class="keyword">MD5</span>(pwd) <span class="keyword">where</span> <span class="keyword">id</span> = <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> testmd5 <span class="keyword">values</span>(<span class="number">4</span>, <span class="string">'xiaoming'</span>, <span class="keyword">MD5</span>(<span class="string">'123456'</span>));</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> testmd5 <span class="keyword">where</span> <span class="string">`name`</span> = <span class="string">'xiaoming'</span> <span class="keyword">and</span> pwd = <span class="keyword">MD5</span>(<span class="string">'123456'</span>);</span><br></pre></td></tr></table></figure>



<h2 id="事务-transaction"><a href="#事务-transaction" class="headerlink" title="事务(transaction)"></a>事务(transaction)</h2><p>要么都成功，要么都失败</p>
<p>================</p>
<p>1.SQL执行 A给B转账</p>
<p>2.SQL执行 B收到A的钱</p>
<p>================</p>
<p>将一组SQL放在一个批次中去执行</p>
<h3 id="事务原则"><a href="#事务原则" class="headerlink" title="事务原则"></a>事务原则</h3><p>ACID原则：<strong>原子性(atomicity)</strong>  <strong>一致性(consistency)  隔离性(isolation)  持久性(durability)</strong> （可能会产生脏读、幻读…）</p>
<p><strong>原子性</strong>： 针对同一个事物的，要么都完成，要么都失败</p>
<p><strong>一致性</strong>：针对一个事务操作前后的状态一致，A+B的总金额不变</p>
<p><strong>隔离性</strong>：A给B转钱  C给B转钱  是互不影响的</p>
<p><strong>持久性</strong>：</p>
<p>操作前 A：800  B： 200</p>
<p>操作后 A： 600  B： 400</p>
<p>如果在操作前（事务还没有提交）服务器宕机或断电，那么重启数据库以后，数据状态： A 800 B 200</p>
<p>如果在操作后（事务已经提交）服务器宕机或断电，那么重启数据库以后，数据状态： A 600 B 400</p>
<p><strong>脏读</strong>：一个事务读取了另一个事务未提交的数据</p>
<p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/329.png" alt="329"></p>
<p><strong>不可重复读</strong></p>
<p>在一个事务内读取表中的某一行数据，多次读取结果不同。</p>
<p>在事务已经提交，B有人转账进来300，那么两次读取B的钱就不同</p>
<p><strong>幻读</strong></p>
<p>一个事务内读取到了别的事务插入的数据，导致前后读取不一致（一般是行影响，多了一行）</p>
<h3 id="事务操作"><a href="#事务操作" class="headerlink" title="事务操作"></a>事务操作</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- mysql 默认开启事务自动提交的</span></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">0</span> <span class="comment">-- 关闭</span></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">1</span> <span class="comment">-- 开启（默认）</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 手动处理事务</span></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">start</span> <span class="keyword">transaction</span>; <span class="comment">-- 标记一个事务的开始</span></span><br><span class="line">...</span><br><span class="line">...</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 提交</span></span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="comment">-- 回滚</span></span><br><span class="line"><span class="keyword">rollback</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 示例</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> shop <span class="built_in">character</span> <span class="keyword">set</span> utf8 <span class="keyword">collate</span> utf8_general_ci;</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">databases</span>;</span><br><span class="line"><span class="keyword">use</span> shop;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">database</span>();</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="string">`account`</span>(</span><br><span class="line"><span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">3</span>) <span class="keyword">not</span> <span class="literal">null</span> auto_increment,</span><br><span class="line"><span class="string">`name`</span> <span class="built_in">varchar</span>(<span class="number">30</span>) <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"><span class="string">`money`</span> <span class="built_in">DECIMAL</span>(<span class="number">9</span>, <span class="number">2</span>) <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line">primary <span class="keyword">key</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">engine</span> = <span class="keyword">innodb</span> <span class="keyword">default</span> <span class="keyword">charset</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">tables</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="string">`account`</span>(<span class="string">`name`</span>, <span class="string">`money`</span>) <span class="keyword">values</span> (<span class="string">'A'</span>, <span class="number">800.00</span>), (<span class="string">'B'</span>, <span class="number">200.00</span>);</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">account</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 模拟转账： 事务</span></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">0</span>; <span class="comment">-- 关闭自动提交</span></span><br><span class="line"><span class="keyword">start</span> <span class="keyword">transaction</span>; <span class="comment">-- 开启一个事务</span></span><br><span class="line"><span class="keyword">update</span> <span class="keyword">account</span> <span class="keyword">set</span> money=money<span class="number">-500</span> <span class="keyword">where</span> <span class="string">`name`</span> = <span class="string">'A'</span>; <span class="comment">-- A减500 </span></span><br><span class="line"><span class="keyword">update</span> <span class="keyword">account</span> <span class="keyword">set</span> money=money+<span class="number">500</span> <span class="keyword">where</span> <span class="string">`name`</span> = <span class="string">'B'</span>; <span class="comment">-- B加500</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">commit</span>; <span class="comment">-- 提交</span></span><br><span class="line"><span class="keyword">rollback</span>; <span class="comment">-- 回滚</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>



<h2 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h2><blockquote>
<p>索引(index)是帮助mysql高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构</p>
</blockquote>
<h3 id="索引分类"><a href="#索引分类" class="headerlink" title="索引分类"></a>索引分类</h3><ul>
<li>主键索引（primary key）<ul>
<li>唯一的标示，只能有一个列作为主键</li>
<li>值不可重复</li>
<li>非空</li>
</ul>
</li>
<li>唯一索引 (unique key)<ul>
<li>避免重复的列出现， 可以有多个唯一索引</li>
<li>值可重复</li>
<li>值可为空</li>
</ul>
</li>
<li>常规索引 (key/index)<ul>
<li>默认的</li>
</ul>
</li>
<li>全文索引 (FullText)</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 显示所有的索引</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">index</span> <span class="keyword">from</span> <span class="keyword">account</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> <span class="string">`account`</span> <span class="keyword">add</span> fulltext <span class="keyword">index</span> <span class="string">`name`</span>(<span class="string">`name`</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建索引的另一种方法</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> 索引名 <span class="keyword">on</span> <span class="keyword">table</span>(<span class="string">`column_name`</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">index</span> <span class="keyword">from</span> <span class="keyword">account</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">explain</span> <span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">account</span> <span class="keyword">where</span> <span class="keyword">match</span>(<span class="keyword">name</span>) against(<span class="string">'A'</span>);</span><br></pre></td></tr></table></figure>



<h3 id="索引原则"><a href="#索引原则" class="headerlink" title="索引原则"></a>索引原则</h3><ul>
<li>索引不是越多越好</li>
<li>不要对经常变动的数据加索引</li>
<li>小数据量的表不需要加索引</li>
<li>索引一般加在常用查询的字段上</li>
</ul>
<p><a href="http://blog.codinglabs.org/articles/theory-of-mysql-index.html" target="_blank" rel="noopener">http://blog.codinglabs.org/articles/theory-of-mysql-index.html</a></p>
<h2 id="权限管理"><a href="#权限管理" class="headerlink" title="权限管理"></a>权限管理</h2><ul>
<li><p>navicat 创建用户</p>
<p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/330.png" alt="330"></p>
</li>
<li><p>SQL命令操作</p>
<p>用户表：mysql.user</p>
<p>本质：对user表进行增删改查</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> mysql.user;</span><br><span class="line"><span class="keyword">use</span> <span class="string">`mysql`</span>;</span><br><span class="line"><span class="comment">-- 创建用户</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span> <span class="keyword">identified</span> <span class="keyword">by</span> <span class="string">'123456'</span>;</span><br><span class="line"><span class="comment">-- 修改当前登录用户的密码</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">password</span> = <span class="keyword">password</span>(<span class="string">'111111'</span>);</span><br><span class="line"><span class="comment">-- (root)修改指定用户的密码</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">password</span> <span class="keyword">form</span> <span class="string">'testa'</span>@<span class="string">'localhost'</span> = <span class="keyword">password</span>(<span class="string">'111111'</span>);</span><br><span class="line"><span class="comment">-- update修改普通用户密码</span></span><br><span class="line"><span class="keyword">UPDATE</span> MySQL.user <span class="keyword">SET</span> authentication_string = <span class="keyword">PASSWORD</span>(<span class="string">"newpwd"</span>) <span class="keyword">WHERE</span> <span class="keyword">User</span> = <span class="string">"username"</span> <span class="keyword">AND</span> Host = <span class="string">"hostname"</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 修改名称</span></span><br><span class="line"><span class="keyword">rename</span> <span class="keyword">user</span> <span class="string">'testa'</span> <span class="keyword">to</span> <span class="string">'testc'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 授权(除了给别人授权，其他都可以)</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">ALL</span> <span class="keyword">PRIVILEGES</span> <span class="keyword">on</span> *.* <span class="keyword">to</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"><span class="comment">-- 单个数据库</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">ALL</span> <span class="keyword">PRIVILEGES</span> <span class="keyword">on</span> shop.* <span class="keyword">to</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查看权限</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">grants</span> <span class="keyword">for</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">grants</span> <span class="keyword">for</span> <span class="string">'root'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 撤销权限</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">ALL</span> <span class="keyword">PRIVILEGES</span> <span class="keyword">on</span> *.* <span class="keyword">to</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"><span class="comment">-- 指定表</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">ALL</span> <span class="keyword">PRIVILEGES</span> <span class="keyword">on</span> shop.* <span class="keyword">from</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除用户</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="string">'testb'</span>@<span class="string">'localhost'</span>;</span><br></pre></td></tr></table></figure>



</li>
</ul>
<h2 id="数据库备份"><a href="#数据库备份" class="headerlink" title="数据库备份"></a>数据库备份</h2><p>备份理由：1.回滚，数据不丢失   2.数据转移</p>
<p><strong>mysql备份方式</strong></p>
<ul>
<li>直接拷贝物理文件</li>
<li>在navicat中导出</li>
<li>使用命令行导出 mysqldump/source</li>
</ul>
<h2 id="数据库设计"><a href="#数据库设计" class="headerlink" title="数据库设计"></a>数据库设计</h2><ul>
<li><p>糟糕的数据库设计：</p>
<p>数据冗余，浪费空间</p>
<p>数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】</p>
<p>程序的性能差</p>
</li>
</ul>
<ul>
<li><p>良好的数据库设计：</p>
<p>解决内存空间</p>
<p>保证数据库的完整性</p>
<p>方便我们开发系统</p>
</li>
</ul>
<p><strong>软件开发中，关于数据库的设计</strong></p>
<ul>
<li>分析需求：分析业务和需要处理的数据库的需求</li>
<li>概要设计：设计关系图E-R图</li>
</ul>
<h3 id="三大范式"><a href="#三大范式" class="headerlink" title="三大范式"></a>三大范式</h3><h3 id="第一范式-1NF-所有属性都必须是单值，无重复的列"><a href="#第一范式-1NF-所有属性都必须是单值，无重复的列" class="headerlink" title="第一范式(1NF): 所有属性都必须是单值，无重复的列"></a>第一范式(1NF): 所有属性都必须是单值，无重复的列</h3><p>以下表违背了第一范式的设计规范</p>
<table>
<thead>
<tr>
<th>姓名</th>
<th>系名</th>
<th>课名</th>
<th>分数</th>
</tr>
</thead>
<tbody><tr>
<td>小明</td>
<td>法律系</td>
<td>高等数学/就业指导</td>
<td>80</td>
</tr>
<tr>
<td>小红</td>
<td>经济系</td>
<td>高等数学/大学英语</td>
<td>70</td>
</tr>
<tr>
<td>小强</td>
<td>建筑系</td>
<td>高等数学/就业指导</td>
<td>75</td>
</tr>
</tbody></table>
<h3 id="第二范式（2NF）：在1NF基础上，所有非主属性必须依赖与所有主属性（主属性只有一个的1NF自动满足2NF）"><a href="#第二范式（2NF）：在1NF基础上，所有非主属性必须依赖与所有主属性（主属性只有一个的1NF自动满足2NF）" class="headerlink" title="第二范式（2NF）：在1NF基础上，所有非主属性必须依赖与所有主属性（主属性只有一个的1NF自动满足2NF）"></a>第二范式（2NF）：在1NF基础上，所有非主属性必须依赖与所有主属性（主属性只有一个的1NF自动满足2NF）</h3><p>简而言之，第二范式就是属性完全依赖于主键</p>
<table>
<thead>
<tr>
<th>学号</th>
<th>姓名</th>
<th>系名</th>
<th>课名</th>
<th>分数</th>
</tr>
</thead>
<tbody><tr>
<td>2021001</td>
<td>小明</td>
<td>法律系</td>
<td>高等数学</td>
<td>80</td>
</tr>
<tr>
<td>2021002</td>
<td>小红</td>
<td>经济系</td>
<td>大学英语</td>
<td>70</td>
</tr>
<tr>
<td>2021003</td>
<td>小强</td>
<td>建筑系</td>
<td>就业指导</td>
<td>75</td>
</tr>
</tbody></table>
<h3 id="第三范式（3NF）：在2NF基础上，所有非主属性必须独立的（单独的）直接依赖于主属性"><a href="#第三范式（3NF）：在2NF基础上，所有非主属性必须独立的（单独的）直接依赖于主属性" class="headerlink" title="第三范式（3NF）：在2NF基础上，所有非主属性必须独立的（单独的）直接依赖于主属性"></a>第三范式（3NF）：在2NF基础上，所有非主属性必须独立的（单独的）直接依赖于主属性</h3><p>一张表只做一件事</p>
<table>
<thead>
<tr>
<th>学号</th>
<th>姓名</th>
<th>系编号</th>
<th>课名</th>
<th>分数</th>
</tr>
</thead>
<tbody><tr>
<td>2021001</td>
<td>小明</td>
<td>101</td>
<td>高等数学</td>
<td>80</td>
</tr>
<tr>
<td>2021002</td>
<td>小红</td>
<td>102</td>
<td>大学英语</td>
<td>70</td>
</tr>
<tr>
<td>2021003</td>
<td>小强</td>
<td>103</td>
<td>就业指导</td>
<td>75</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th>系编号</th>
<th>系名</th>
</tr>
</thead>
<tbody><tr>
<td>101</td>
<td>法律系</td>
</tr>
<tr>
<td>102</td>
<td>经济系</td>
</tr>
<tr>
<td>103</td>
<td>建筑系</td>
</tr>
</tbody></table>
<h3 id="规范性和性能的问题"><a href="#规范性和性能的问题" class="headerlink" title="规范性和性能的问题"></a>规范性和性能的问题</h3><p>有时故意给某些表增加冗余的字段（从多表查询变为乐单表查询）</p>
<h2 id="JDBC"><a href="#JDBC" class="headerlink" title="JDBC"></a>JDBC</h2><h3 id="数据库驱动"><a href="#数据库驱动" class="headerlink" title="数据库驱动"></a>数据库驱动</h3><p>我们的程序需要通过各数据库的驱动来和数据库打交道</p>
<h3 id="JDBC-1"><a href="#JDBC-1" class="headerlink" title="JDBC"></a>JDBC</h3><p>java操作数据库的规范</p>
<p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/338.png" alt="338"></p>
<h3 id="第一个JDBC程序"><a href="#第一个JDBC程序" class="headerlink" title="第一个JDBC程序"></a>第一个JDBC程序</h3><ul>
<li><p>创建数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">create database jdbcStudy character set utf8 collate utf8_general_ci;</span><br><span class="line">use jdbcStudy;</span><br><span class="line"></span><br><span class="line">create table users (</span><br><span class="line"> id int primary key,</span><br><span class="line"> name varchar(40),</span><br><span class="line"> password varchar(40),</span><br><span class="line"> email varchar(60),</span><br><span class="line"> birthday date</span><br><span class="line">); </span><br><span class="line"></span><br><span class="line">insert into users(id,&#96;name&#96;,&#96;password&#96;,email,birthday)</span><br><span class="line">values(1, &#39;zhangsan&#39;,&#39;123456&#39;,&#39;zs@163.com&#39;,&#39;1980-12-04&#39;),</span><br><span class="line">(2, &#39;lisi&#39;,&#39;123456&#39;,&#39;lisi@163.com&#39;,&#39;1981-12-04&#39;),</span><br><span class="line">(3, &#39;wangwu&#39;,&#39;123456&#39;,&#39;wangwu@163.com&#39;,&#39;1982-12-04&#39;);</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建项目</p>
</li>
<li><p>导入驱动jar</p>
<p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/342.png" alt="342"></p>
</li>
<li><p>编写程序</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.xing.lesson01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">JdbcFirstDemo</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException, ClassNotFoundException </span>&#123;</span><br><span class="line">        <span class="comment">// 1.加载驱动</span></span><br><span class="line">        Class.forName(<span class="string">"com.mysql.jdbc.Driver"</span>); <span class="comment">// 固定写法</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">// 2.连接信息，用户信息和url</span></span><br><span class="line">        String url = <span class="string">"jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true"</span>;</span><br><span class="line">        String username = <span class="string">"root"</span>;</span><br><span class="line">        String password = <span class="string">"123456"</span>;</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 3.连接成功，数据库对象 connection 代表数据库</span></span><br><span class="line">        Connection connection = DriverManager.getConnection(url, username, password);</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 4.执行sql的对象 Statement 执行sql的对象</span></span><br><span class="line">        Statement statement = connection.createStatement();</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 5.执行sql的对象 去 执行sql</span></span><br><span class="line">        String sql = <span class="string">"select * from users"</span>;</span><br><span class="line">        ResultSet resultSet = statement.executeQuery(sql); <span class="comment">// 返回的结果集</span></span><br><span class="line"></span><br><span class="line">        <span class="keyword">while</span>(resultSet.next()) &#123;</span><br><span class="line">            System.out.println(<span class="string">"id="</span> + resultSet.getObject(<span class="string">"id"</span>));</span><br><span class="line">            System.out.println(<span class="string">"name="</span> + resultSet.getObject(<span class="string">"name"</span>));</span><br><span class="line">            System.out.println(<span class="string">"pwd="</span> + resultSet.getObject(<span class="string">"password"</span>));</span><br><span class="line">            System.out.println(<span class="string">"email="</span> + resultSet.getObject(<span class="string">"email"</span>));</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 6.释放连接</span></span><br><span class="line">        resultSet.close();</span><br><span class="line">        statement.close();</span><br><span class="line">        connection.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>



</li>
</ul>
<blockquote>
<p>DriverManager</p>
</blockquote>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// DriverManager.registerDriver(new com.mysql.jdbc.Driver());</span></span><br><span class="line">Class.forName(<span class="string">"com.mysql.jdbc.Driver"</span>); <span class="comment">// 固定写法</span></span><br></pre></td></tr></table></figure>



<blockquote>
<p>URL</p>
</blockquote>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">String url = <span class="string">"jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true"</span>;</span><br></pre></td></tr></table></figure>



<blockquote>
<p>Connection</p>
</blockquote>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 数据库对象</span></span><br><span class="line">Connection connection = DriverManager.getConnection(url, username, password);</span><br><span class="line">connection.rollback();</span><br><span class="line">connection.commit();</span><br><span class="line">connection.setAutoCommit();</span><br></pre></td></tr></table></figure>



<blockquote>
<p>Statement</p>
</blockquote>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 执行sql的对象</span></span><br><span class="line">statement.executeQuery(); <span class="comment">// 查询操作返回 ResultSet</span></span><br><span class="line">statement.execute();</span><br><span class="line">statement.executeUpdate(); <span class="comment">// 更新、插入、删除，都是用这个，返回一个受影响的行数</span></span><br></pre></td></tr></table></figure>



<blockquote>
<p>ResultSet 结果集</p>
</blockquote>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">resultSet.previous();</span><br><span class="line">resultSet.beforeFirst();</span><br><span class="line">resultSet.absolute(<span class="number">2</span>);</span><br><span class="line"></span><br><span class="line">resultSet.getObject();</span><br><span class="line">resultSet.getString();</span><br><span class="line">resultSet.getInt();</span><br><span class="line">resultSet.getDouble();</span><br><span class="line">resultSet.getFloat();</span><br></pre></td></tr></table></figure>



<blockquote>
<p>释放资源</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">resultSet.close();</span><br><span class="line">statement.close();</span><br><span class="line">connection.close();</span><br></pre></td></tr></table></figure>



<h3 id="JDBC工具类"><a href="#JDBC工具类" class="headerlink" title="JDBC工具类"></a>JDBC工具类</h3><p>db.properties</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">driver=com.mysql.jdbc.Driver</span><br><span class="line">url=jdbc:mysql:<span class="comment">//localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</span></span><br><span class="line">username=root</span><br><span class="line">password=******</span><br></pre></td></tr></table></figure>

<p>utils.JdbaUtils</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.xing.lesson02.utils;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.io.IOException;</span><br><span class="line"><span class="keyword">import</span> java.io.InputStream;</span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">import</span> java.util.Properties;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">JdbcUtils</span> </span>&#123;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String driver = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String url = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String username = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String password = <span class="keyword">null</span>;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">static</span> &#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");</span><br><span class="line">            Properties properties = <span class="keyword">new</span> Properties();</span><br><span class="line">            properties.load(in);</span><br><span class="line"></span><br><span class="line">            driver = properties.getProperty(<span class="string">"driver"</span>);</span><br><span class="line">            url = properties.getProperty(<span class="string">"url"</span>);</span><br><span class="line">            username = properties.getProperty(<span class="string">"username"</span>);</span><br><span class="line">            password = properties.getProperty(<span class="string">"password"</span>);</span><br><span class="line"></span><br><span class="line">            Class.forName(<span class="string">"com.mysql.jdbc.Driver"</span>);</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">catch</span> (IOException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; <span class="keyword">catch</span> (ClassNotFoundException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 获取连接</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> Connection <span class="title">getConnection</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> DriverManager.getConnection(url, username, password);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 释放资源</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">release</span><span class="params">(Connection conn, Statement st, ResultSet rs)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        <span class="keyword">if</span> (rs != <span class="keyword">null</span>) &#123;</span><br><span class="line">            rs.close();</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (st != <span class="keyword">null</span>) &#123;</span><br><span class="line">            st.close();</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (conn != <span class="keyword">null</span>) &#123;</span><br><span class="line">            conn.close();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>工具类的实现</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.xing.lesson02.utils;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">TestInsert</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        Connection connection = <span class="keyword">null</span>;</span><br><span class="line">        Statement st = <span class="keyword">null</span>;</span><br><span class="line">        ResultSet rs = <span class="keyword">null</span>;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            connection  =  JdbcUtils.getConnection(); <span class="comment">// 获取数据库连接</span></span><br><span class="line">            st = connection.createStatement(); <span class="comment">// sql执行对象</span></span><br><span class="line">            String sql = <span class="string">"insert into users(id,`name`,`password`,email,birthday)"</span> +</span><br><span class="line">                    <span class="string">"values(4, 'xing','123456','xing@163.com','1980-12-04')"</span>;</span><br><span class="line"></span><br><span class="line">            <span class="keyword">int</span> i = st.executeUpdate(sql);</span><br><span class="line">            <span class="keyword">if</span> (i &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                System.out.println(<span class="string">"插入成功～"</span>);</span><br><span class="line">            &#125;</span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            JdbcUtils.release(connection, st, rs);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>



<blockquote>
<p>SQL注入问题</p>
</blockquote>
<p><a href="https://www.cnblogs.com/myseries/p/10821372.html" target="_blank" rel="noopener">https://www.cnblogs.com/myseries/p/10821372.html</a></p>
<h3 id="preparedStatement"><a href="#preparedStatement" class="headerlink" title="preparedStatement"></a>preparedStatement</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line">package com.xing.lesson03;</span><br><span class="line"></span><br><span class="line">import com.xing.lesson02.utils.JdbcUtils;</span><br><span class="line"></span><br><span class="line">import java.sql.Connection;</span><br><span class="line">import java.util.Date;</span><br><span class="line">import java.sql.PreparedStatement;</span><br><span class="line">import java.sql.SQLException;</span><br><span class="line"></span><br><span class="line">public class TestInsert &#123;</span><br><span class="line">    public static void main(String[] args) throws SQLException &#123;</span><br><span class="line">        Connection connection = null;</span><br><span class="line">        PreparedStatement st = null;</span><br><span class="line">        try &#123;</span><br><span class="line">            connection = JdbcUtils.getConnection();</span><br><span class="line">            // 区别</span><br><span class="line">            // 使用？占位符代替参数</span><br><span class="line">            String sql = "<span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">users</span>(<span class="keyword">id</span>,<span class="string">`name`</span>,<span class="string">`password`</span>,email,birthday) <span class="keyword">values</span>(?, ?, ?, ?, ?)<span class="string">";</span></span><br><span class="line"><span class="string">            st = connection.prepareStatement(sql); // 预编译SQL 先写sql 然后不执行</span></span><br><span class="line"><span class="string">            // 手动给参数赋值</span></span><br><span class="line"><span class="string">            st.setInt(1, 4);</span></span><br><span class="line"><span class="string">            st.setString(2, "</span>xing<span class="string">");</span></span><br><span class="line"><span class="string">            st.setString(3, "</span><span class="number">123456</span><span class="string">");</span></span><br><span class="line"><span class="string">            st.setString(4, "</span><span class="number">123456</span>@qq.com<span class="string">");</span></span><br><span class="line"><span class="string">            // 注意点 sql.Date 数据库</span></span><br><span class="line"><span class="string">            //       util.Date Java new Date().getTime() 获取时间戳</span></span><br><span class="line"><span class="string">            st.setDate(5, new java.sql.Date(new Date().getTime()));</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">            // 执行</span></span><br><span class="line"><span class="string">            int i = st.executeUpdate();</span></span><br><span class="line"><span class="string">            if (i &gt; 0) &#123;</span></span><br><span class="line"><span class="string">                System.out.println("</span>插入成功～<span class="string">");</span></span><br><span class="line"><span class="string">            &#125;</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">        &#125; catch (SQLException e) &#123;</span></span><br><span class="line"><span class="string">            e.printStackTrace();</span></span><br><span class="line"><span class="string">        &#125;  finally &#123;</span></span><br><span class="line"><span class="string">            JdbcUtils.release(connection, st, null);</span></span><br><span class="line"><span class="string">        &#125;</span></span><br><span class="line"><span class="string">    &#125;</span></span><br><span class="line"><span class="string">&#125;</span></span><br></pre></td></tr></table></figure>



<h3 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.xing.lesson04;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> com.xing.lesson02.utils.JdbcUtils;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.PreparedStatement;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">TestTransaction</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        Connection connection = <span class="keyword">null</span>;</span><br><span class="line">        PreparedStatement st = <span class="keyword">null</span>;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            connection = JdbcUtils.getConnection();</span><br><span class="line">            <span class="comment">// 关闭数据库的自动提交，自动会开启事务</span></span><br><span class="line">            connection.setAutoCommit(<span class="keyword">false</span>);</span><br><span class="line">            String sql1 = <span class="string">"update account set money = money - 100 where name = 'A'"</span>;</span><br><span class="line">            st = connection.prepareStatement(sql1); <span class="comment">// 预编译SQL 先写sql 然后不执行</span></span><br><span class="line">            st.executeUpdate();</span><br><span class="line">            String sql2 = <span class="string">"update account set money = money + 100 where name = 'B'"</span>;</span><br><span class="line">            st = connection.prepareStatement(sql2); <span class="comment">// 预编译SQL 先写sql 然后不执行</span></span><br><span class="line">            st.executeUpdate();</span><br><span class="line"></span><br><span class="line">            <span class="comment">// 业务完毕 提交事务</span></span><br><span class="line">            connection.commit();</span><br><span class="line">            System.out.println(<span class="string">"成功～"</span>);</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            connection.rollback();</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;  <span class="keyword">finally</span> &#123;</span><br><span class="line">            JdbcUtils.release(connection, st, <span class="keyword">null</span>);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>



<h3 id="大家在看"><a href="#大家在看" class="headerlink" title="大家在看"></a>大家在看</h3><ul>
<li><a href="http://mp.weixin.qq.com/s?__biz=MzUxNzU2ODExNA==&mid=2247484041&idx=1&sn=c3d76bf87826afd2291db7499c0ceb8a&chksm=f9976194cee0e882e0447f5b1fca5b4580c287231815cf8241f4c0d4ed21bc03deb8a6099641&scene=21#wechat_redirect" target="_blank" rel="noopener">面试官：遇到一个从没接触过的问题你是怎么解决的？</a></li>
<li><a href="http://mp.weixin.qq.com/s?__biz=MzUxNzU2ODExNA==&mid=2247483990&idx=1&sn=7e7bc3c51e924a0a388d0792cd952bd1&chksm=f997614bcee0e85da22d80084522a41633532745be1054dedd14f9d48dabe8017c29afb16331&scene=21#wechat_redirect" target="_blank" rel="noopener">开发小程序分页功能时的这些坑你遇到过吗？</a></li>
<li><a href="http://mp.weixin.qq.com/s?__biz=MzUxNzU2ODExNA==&mid=2247483926&idx=1&sn=68aabf6a568c76d0e6ad7dfc9ca40b0b&chksm=f997610bcee0e81de7cc8ec9bfd459289d03f5358937ee0b60719396cc08969406c68e66c17a&scene=21#wechat_redirect" target="_blank" rel="noopener">live-server本地搭建临时服务</a></li>
</ul>
<h3 id="关注公众号：-页面仔小杨-【实战干货、原创分享】"><a href="#关注公众号：-页面仔小杨-【实战干货、原创分享】" class="headerlink" title="关注公众号： 页面仔小杨 【实战干货、原创分享】"></a>关注公众号： 页面仔小杨 【实战干货、原创分享】</h3><p><img src="https://raw.githubusercontent.com/itxing666/blogImgs/main/img/bottom.png" alt="bottom"></p>
</div></article></div><div class="right-container"><div class="widget"><div id="arAnchorBar"></div></div></div></section></div><div class="right-menu"></div><div class="modal search-modal"><div class="input-field"><input type="text" id="search_input"><label for="search-input">搜索</label></div><div id="search_result" class="search-result"></div></div><div class="blog-overlay"></div><footer class="row"><div class="footer-con"><div class="paginator"><a href="/2021/10/15/Java/Docker%E5%85%A5%E9%97%A8/" title="Docker入门" class="prev">PREV</a><a href="/2021/08/23/mini-program/%E5%BE%AE%E4%BF%A1%E5%B0%8F%E7%A8%8B%E5%BA%8F%E5%9B%BE%E7%89%87%E6%B5%81%E6%88%96%E6%9C%AC%E5%9C%B0%E5%9B%BE%E7%89%87%E8%BD%ACbase64%E5%A4%84%E7%90%86%E6%96%B9%E6%A1%88/" title="微信小程序图片流&amp;本地图片转base64处理方案" class="next">NEXT</a></div><a href="#comment" class="comment-anchor"></a><div id="vcomments"></div><script>new Valine({
    el: "#vcomments",
    appId: "fTqcCRR2oHhKQGgjSy86Ifkf-gzGzoHsz",
    appKey: "r1WK7286QLlfxnpbWsjs06xH",
    notify: false,
    verify: false,
    avatar: "robohash",
    visitor: true,
    placeholder: "随便说点什么～.～",
});</script><div class="copyright"><p>Copyright © 2016 - 2022  <a target="_blank">页面仔小杨</a> 版权所有【实战干货、原创分享】 <br></p><p> <span style="padding-right: 6px;">关注公众号：页面仔小杨</span></p></div><div class="totop"><i></i></div></div></footer><script async src="https://cdn.bootcss.com/mathjax/2.6.1/MathJax.js?config=TeX-MML-AM_CHTML"></script><script src="/scripts/jquery-1.8.2.min.js"></script><script src="/scripts/ar-anchor.js"></script><script src="/scripts/main.js"></script><script>(function(b,o,i,l,e,r){b.GoogleAnalyticsObject=l;b[l]||(b[l]=function(){(b[l].q=b[l].q||[]).push(arguments)});b[l].l=+new Date;e=o.createElement(i);r=o.getElementsByTagName(i)[0];e.src='//www.google-analytics.com/analytics.js';r.parentNode.insertBefore(e,r)}(window,document,'script','ga'));ga('create',"UA-65933410-1",'auto');ga('send','pageview');</script></body></html>